package org.using_jdbc;

import com.mysql.cj.jdbc.DatabaseMetaData;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class CreatTable {
    Connection connection;
    File file;
    static final int MAXVARCHARLEN = 50;

    public  CreatTable(Connection connection, File file){
        this.connection =connection;
        this.file=file;
    }


    /**
     * @description: 执行根据文件在数据库中中创建表的操作
     * @author: HouBinyang
     * @date: 2023/10/12 0:49
     * @param: []
     * @return: void
     **/
    public boolean create_table_auto_index_with_repeat() throws SQLException{
        //检测数据库中是否存在同名表结构
        String file_name_without_suffix= file.getName().substring(0, file.getName().lastIndexOf('.')).toLowerCase();
        DatabaseMetaData dbMetaData = (DatabaseMetaData) connection.getMetaData();
        ResultSet resultSet = dbMetaData.getTables(null, null, null,new String[] { "TABLE" });
        while(resultSet.next())
        {
            if(resultSet.getString("TABLE_NAME").equals(file_name_without_suffix)){
                System.out.println("数据库中已存在表:"+file_name_without_suffix);
                return false;
            }
        }

        // 读取文件
        try (Scanner scanner = new Scanner(file, StandardCharsets.UTF_8)) {
            String DELIMITER = ",";
            scanner.useDelimiter(",");

            //检测文件是否为空
            if(scanner.hasNextLine()){
                // 读取列名
                Scanner columns_scanner = new Scanner(scanner.nextLine());
                List<String> columns = new ArrayList<>();
                columns_scanner.useDelimiter(DELIMITER);
                while (columns_scanner.hasNext()) {
                    columns.add(columns_scanner.next());
                }

                //初始化变量
                Statement stmt;
                System.out.println(" 实例化Statement对象...");
                stmt = connection.createStatement();


                String create_table_sql = sql_create_table_auto_index(file_name_without_suffix, columns);
                //执行sql语句
                try {
                    System.out.println("创建数据表中...");
                    stmt.executeUpdate(create_table_sql);
                    sql_log(create_table_sql,"sql_creat_table.log");
                    System.out.println("创建成功！");
                }catch (SQLException se){
                    se.printStackTrace();
                    System.exit(-1);
                }

                //插入数据sql
                String insert_sql = "INSERT INTO " + file_name_without_suffix + " VALUES(null" + ",?".repeat(columns.size()) +
                        ")";
                //执行sql
                PreparedStatement pStmt = connection.prepareStatement(insert_sql);
                int row_count = 0;
                while (scanner.hasNextLine()){
                    Scanner line_scanner = new Scanner(scanner.nextLine());
                    line_scanner.useDelimiter(DELIMITER);
                    for(int i=0;i<columns.size();i++){
                        if(line_scanner.hasNext()){
                            pStmt.setString(i+1,line_scanner.next());
                        }else {
                            pStmt.setString(i+1,null);
                            System.out.println("[\t"+row_count+"\t,\t"+(i+1)+"\t]处数据为空");
                        }
                    }
                    try{
                        pStmt.executeUpdate();
                        sql_log(pStmt.toString(),"sql_insert_log");
                    }catch (SQLException se){
                        se.printStackTrace();
                    }
                    row_count++;
                }
                // 关闭资源
                try{
                    stmt.close();
                }catch(SQLException ignored){
                }// 什么都不做
                return true;
            }
            else{
                System.out.println("文件为空");
                return false;
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public boolean create_table_auto_index_without_repeat() throws SQLException {
        //检测数据库中是否存在同名表结构
        String file_name_without_suffix= file.getName().substring(0, file.getName().lastIndexOf('.')).toLowerCase();
        DatabaseMetaData dbMetaData = (DatabaseMetaData) connection.getMetaData();
        ResultSet resultSet = dbMetaData.getTables(null, null, null,new String[] { "TABLE" });
        while(resultSet.next())
        {
            if(resultSet.getString("TABLE_NAME").equals(file_name_without_suffix)){
                System.out.println("数据库中已存在表:"+file_name_without_suffix);
                return false;
            }
        }

        // 读取文件
        try (Scanner scanner = new Scanner(file, StandardCharsets.UTF_8)) {
            String DELIMITER = ",";
            scanner.useDelimiter(",");

            //检测文件是否为空
            if(scanner.hasNextLine()){
                // 读取列名
                Scanner columns_scanner = new Scanner(scanner.nextLine());
                List<String> columns = new ArrayList<>();
                columns_scanner.useDelimiter(DELIMITER);
                while (columns_scanner.hasNext()) {
                    columns.add(columns_scanner.next());
                }

                //初始化变量
                Statement stmt;
                System.out.println(" 实例化Statement对象...");
                stmt = connection.createStatement();

                String create_table_sql = sql_create_table_auto_index(file_name_without_suffix, columns);
                //执行sql语句
                try {
                    System.out.println("创建数据表中...");
                    stmt.executeUpdate(create_table_sql);
                    sql_log(create_table_sql,"sql_creat_table.log");
                    System.out.println("创建成功！");
                }catch (SQLException se){
                    se.printStackTrace();
                    System.exit(-1);
                }

                //插入数据sql
                String insert_sql = "INSERT INTO " + file_name_without_suffix + " VALUES(null" + ",?".repeat(columns.size()) +
                        ")";
                //执行sql
                PreparedStatement pStmt = connection.prepareStatement(insert_sql);
                int row_count = 0;
                //去重
                List<String> data = remove_repeat(scanner);
                for(String x :data){
                    Scanner line_scanner = new Scanner(x);
                    line_scanner.useDelimiter(DELIMITER);
                    for(int i=0;i<columns.size();i++){
                        if(line_scanner.hasNext()){
                            pStmt.setString(i+1,line_scanner.next());
                        }else {
                            pStmt.setString(i+1,null);
                            System.out.println("[\t"+row_count+"\t,\t"+(i+1)+"\t]处数据为空");
                        }
                    }
                    try{
                        pStmt.executeUpdate();
                        sql_log(pStmt.toString(),"sql_insert_log");
                    }catch (SQLException se){
                        se.printStackTrace();
                    }
                    row_count++;
                }
                // 关闭资源
                try{
                    stmt.close();
                }catch(SQLException ignored){
                }// 什么都不做
                return true;
            }
            else{
                System.out.println("文件为空");
                return false;
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public boolean create_table_with_primary_keys(List<String> primaryKeys) throws SQLException {
        //检测数据库中是否存在同名表结构
        String file_name_without_suffix= file.getName().substring(0, file.getName().lastIndexOf('.')).toLowerCase();
        DatabaseMetaData dbMetaData = (DatabaseMetaData) connection.getMetaData();
        ResultSet resultSet = dbMetaData.getTables(null, null, null,new String[] { "TABLE" });
        while(resultSet.next())
        {
            if(resultSet.getString("TABLE_NAME").equals(file_name_without_suffix)){
                System.out.println("数据库中已存在表:"+file_name_without_suffix);
                return false;
            }
        }
        // 读取文件
        try (Scanner scanner = new Scanner(file, StandardCharsets.UTF_8)) {
            String DELIMITER = ",";
            scanner.useDelimiter(",");

            //检测文件是否为空
            if(scanner.hasNextLine()){
                // 读取列名并除去 “ 符号
                Scanner columns_scanner = new Scanner(scanner.nextLine());
                List<String> columns = new ArrayList<>();
                columns_scanner.useDelimiter(DELIMITER);
                while (columns_scanner.hasNext()) {
                    columns.add(columns_scanner.next());
                }
                for (int i=0;i<columns.size();i++) {
                    if(columns.get(i).indexOf('\"')>=0){
                        System.out.println("检测到列名中含有 \" 字符，已自动去除并继续执行程序");
                        String new_column=columns.get(i).replaceAll("\"","");
                        columns.remove(columns.get(i));
                        columns.add(i,new_column);
                    }
                }

                //若不指定primary keys。
                if(primaryKeys==null){
                    primaryKeys = columns;
                }
                else {
                    //检测主键是否都在列名里面
                    for (String key : primaryKeys){
                        boolean exist = false;
                        for (String column : columns){
                            if(column.equals(key)){
                                exist = true;
                                break;
                            }
                        }
                        if(!exist){
                            System.out.println("主键: "+key+" 不在列名中。");
                            return false;
                        }
                    }
                }

                //初始化变量
                Statement stmt;
                System.out.println(" 实例化Statement对象...");
                stmt = connection.createStatement();

                String create_table_sql = sql_create_table_with_primary_keys(file_name_without_suffix, columns,primaryKeys);
                //执行sql语句
                try {
                    System.out.println("创建数据表中...");
                    stmt.executeUpdate(create_table_sql);
                    sql_log(create_table_sql,"sql_creat_table.log");
                    System.out.println("创建成功！");
                }catch (SQLException se){
                    se.printStackTrace();
                    System.exit(-1);
                }

                //插入数据sql
                String insert_sql = "INSERT INTO " + file_name_without_suffix + " VALUES("  + "?,".repeat(columns.size()) + ")";
                insert_sql = insert_sql.replace(",)",")");
                //执行sql
                PreparedStatement pStmt = connection.prepareStatement(insert_sql);
                int row_count = 0;
                //去重
                List<String> data = remove_repeat(scanner);
                for(String x :data){
                    Scanner line_scanner = new Scanner(x);
                    line_scanner.useDelimiter(DELIMITER);
                    for(int i=0;i<columns.size();i++){
                        if(line_scanner.hasNext()){
                            pStmt.setString(i+1,line_scanner.next());
                        }else {
                            //检测是否为主键
                            boolean is_key = false;
                            for (String key : primaryKeys){
                                if(key.equals(columns.get(i))){
                                    is_key = true;
                                    break;
                                }
                            }
                            if(is_key){
                                System.out.println("在第"+row_count + "行，"+"主键: "+columns.get(i)+" 值为空。");
                                stmt.execute("DROP TABLE "+file_name_without_suffix +";");
                                System.out.println("创建的表结构已自动删除");
                                // 关闭资源
                                try{
                                    stmt.close();
                                }catch(SQLException ignored){
                                }// 什么都不做
                                return false;
                            }
                            pStmt.setString(i+1,null);
                            System.out.println("[\t"+row_count+"\t,\t"+(i+1)+"\t]处数据为空");
                        }
                    }
                    try{
                        pStmt.executeUpdate();
                        sql_log(pStmt.toString(),"sql_insert_log");

                    }catch (SQLException se){
                        System.out.println(pStmt +"  插入失败，主键重复项");
                    }
                    row_count++;
                }
                // 关闭资源
                try{
                    stmt.close();
                }catch(SQLException ignored){
                }// 什么都不做
                return true;
            }
            else{
                System.out.println("文件为空");
                return false;
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        // 什么都不做
        return false;
    }

    private String sql_create_table_with_primary_keys(String fileNameWithoutSuffix, List<String> columns, List<String> primaryKeys) {
        //拼接SQL语句创建表
        StringBuilder create_table_sql = new StringBuilder("CREATE TABLE IF NOT EXISTS " + fileNameWithoutSuffix + " (");
        for (String column : columns) {
            if(column.indexOf('\"')>=0){
                System.out.println("检测到列名中含有 \" 字符，已自动去除并继续执行程序");
                column=column.replaceAll("\"","");
            }
            create_table_sql.append(column).append(" VARCHAR(").append(MAXVARCHARLEN).append("), ");
        }
        create_table_sql.append("PRIMARY KEY ( ");
        for (String key : primaryKeys) {
            if(key.indexOf('\"')>=0){
                System.out.println("检测到主键中含有 \" 字符，已自动去除并继续执行程序");
                key=key.replaceAll("\"","");
            }
            create_table_sql.append(key).append(",");
        }
        create_table_sql.deleteCharAt(create_table_sql.length()-1);
        create_table_sql.append(" ))CHARSET=utf8;");
        return create_table_sql.toString();
    }


    private static List<String> remove_repeat(Scanner scanner) {
        List<String> data = new ArrayList<>();
        boolean repeat = false;
        String add_str;
        while (scanner.hasNextLine()){
            add_str= scanner.nextLine();
            for (String x : data){
                if (x.equals(add_str)) {
                    repeat = true;
                    break;
                }
            }
            if(repeat){
                System.out.println("检测到表格文件中的重复项: "+add_str);
            } else {
                data.add(add_str);
            }
            repeat = false;
        }
        return data;
    }

    private static String sql_create_table_auto_index(String file_name_without_suffix, List<String> columns) {
        //拼接SQL语句创建表
        StringBuilder create_table_sql = new StringBuilder("CREATE TABLE IF NOT EXISTS " + file_name_without_suffix + " ("
                + file_name_without_suffix + "_auto_index" + " INT NOT NULL AUTO_INCREMENT, ");
        for (String column : columns) {
            if(column.indexOf('\"')>=0){
                System.out.println("检测到列名中含有 \" 字符，已自动去除并继续执行程序");
                column=column.replaceAll("\"","");
            }
            create_table_sql.append(column).append(" VARCHAR(").append(MAXVARCHARLEN).append("), ");
        }
        create_table_sql.append("PRIMARY KEY ( ").append(file_name_without_suffix).append("_auto_index").append(" ))CHARSET=utf8;");
        return create_table_sql.toString();
    }

    private static void sql_log(String sql,String filename) throws IOException {
        StringBuilder stringBuilder = new StringBuilder();
        BufferedWriter bw = new BufferedWriter(new FileWriter(filename,true));
        DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        stringBuilder.append("Time :").append(fmt.format(LocalDateTime.now())).append('\n');
        stringBuilder.append(sql).append("\n");
        bw.write(stringBuilder.toString());
        bw.flush();
        bw.close();
    }


}


